﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace baroIstatistik.Class
{
    public class context
    {

        public static string[] loginkontrol(string kullaniciadi, string parola)
        {
            string[] uyebilgileri = new string[5];
            uyebilgileri[0] = null;
            uyebilgileri[1] = null;
            uyebilgileri[2] = null;
            uyebilgileri[3] = null;
            uyebilgileri[4] = null;
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from userTable where username=@usr and password=@pass", sqlcon);
            sqlcmd.Parameters.AddWithValue("@usr", kullaniciadi);
            sqlcmd.Parameters.AddWithValue("@pass", parola);


            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();


            while (sdr.Read())
            {

                uyebilgileri[0] = "var";
                uyebilgileri[1] = sdr["name"].ToString();
                uyebilgileri[2] = sdr["surname"].ToString();
                uyebilgileri[3] = sdr["regDate"].ToString();
                uyebilgileri[4] = sdr["userID"].ToString();
            }

            sqlcon.Close();

            try
            {


                //SqlCommand sqlcmd2 = new SqlCommand("delete from basvuru where baglamadurumu=2 and userID=" + uyebilgileri[4], sqlcon);

                SqlCommand sqlcmd3 = new SqlCommand("delete from cocuk where baglamadurumu=2 and userID=@userID", sqlcon);
                sqlcmd3.Parameters.AddWithValue("@userID", uyebilgileri[4]);

                sqlcon.Open();
                //sqlcmd2.ExecuteNonQuery();
                sqlcmd3.ExecuteNonQuery();
                sqlcon.Close();

            }
            catch (Exception)
            {


            }

            return uyebilgileri;


        }

        public static ArrayList sehirlerigetir()
        {
            ArrayList sehirler = new ArrayList();
            SqlConnection sqlcon = connection.baglanti;

            SqlCommand sqlcmd = new SqlCommand("select * from sehirler2", sqlcon);

            sqlcon.Open();


            SqlDataReader sdr = sqlcmd.ExecuteReader();

            while (sdr.Read())
            {
                sehirler.Add(sdr["name"].ToString());


            }

            sqlcon.Close();


            return sehirler;
        }

        public static ArrayList kurumlargetir()
        {

            ArrayList ar = new ArrayList();


            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand(  "select * from kurumlar",sqlcon   );
            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();

            while (sdr.Read())
	{
        ar.Add(sdr["kurum"]);
	}

            sqlcon.Close();


            return ar;

        }


        public static ArrayList karakolgetir()
        {

            ArrayList karakol = new ArrayList();


            SqlConnection sqlcon = connection.baglanti;


            SqlCommand swlcmd = new SqlCommand("select * from karakollar", sqlcon);


            sqlcon.Open();


            SqlDataReader sdr = swlcmd.ExecuteReader();
            while (sdr.Read())
            {

                karakol.Add(sdr["karakol"].ToString());



            }


            sqlcon.Close();


            return karakol;



        }


        public static ArrayList sabitadresleri_getir()
        {
            ArrayList sbt = new ArrayList();

            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from adresler ", sqlcon);
            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();

            while (sdr.Read())
            {

                sbt.Add(sdr["adress"].ToString());


            }

            sqlcon.Close();





            return sbt;

        }

        public static int yenikayityarat(int userID)
        {
            int basvuruID = -1;
            SqlConnection sqlcon = connection.baglanti;
           
                SqlCommand sqlcmd3 = new SqlCommand("select basvuruID from basvuru where baglamadurumu=2 and userID=@uid ", sqlcon);
                sqlcmd3.Parameters.AddWithValue("@uid", userID);
                sqlcon.Open();
                SqlDataReader sdr = sqlcmd3.ExecuteReader();
                while (sdr.Read())
                {
                    basvuruID = Convert.ToInt32(sdr["basvuruID"]);
                }

                sqlcon.Close();
                if (basvuruID != -1)
                {
                    return basvuruID;
                }
                else
                {

                    SqlCommand sqlcmd = new SqlCommand("insert into basvuru (baglamadurumu,userID) values(2,@uid)", sqlcon);
                    sqlcmd.Parameters.AddWithValue("@uid", userID);

                    sqlcon.Open();

                    sqlcmd.ExecuteNonQuery();

                    sqlcon.Close();

                    SqlCommand sqlcmd2 = new SqlCommand("select basvuruID from basvuru where baglamadurumu=2 and userID=@uid ", sqlcon);
                    sqlcmd2.Parameters.AddWithValue("@uid", userID);

                    sqlcon.Open();

                    SqlDataReader sdr2 = sqlcmd2.ExecuteReader();

                    while (sdr2.Read())
                    {
                        basvuruID = Convert.ToInt32(sdr2["basvuruID"]);

                    }



                    sqlcon.Close();



                }





            return basvuruID;
        }

        public static ArrayList islerigetir()
        {
            ArrayList ar = new ArrayList();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from meslekler",sqlcon);
            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                ar.Add(sdr["isAdi"]);

                
            }

            sqlcon.Close();

            return ar;
        }

   

        public static List<basvur> appgetir()
        {

            List<basvur> appler = new List<basvur>();



            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from basvuru  as b left join karar as k on b.basvuruID=k.basvuruID where baglamadurumu=0 ", sqlcon);

            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();

            while (sdr.Read())
            {

                basvur l = new basvur();
                l.BasvuruID  = Convert.ToInt32(sdr["basvuruID"]);

                l.Isim = sdr["isim"].ToString();
                l.esas = sdr["dosyano"].ToString();
                string karar = sdr["kararvarmi"].ToString();
                DateTime kd = Convert.ToDateTime(sdr["kayittarihi"]);
                l.Kayittarihi2 = kd.ToString("d MMM yyyy ").Substring(0, 10);




                if (karar != "0")
                {
                    l.status = "Karar Güncelle";
                    l.butontip = "btn btn-warning";
                    l.durum = "Var";
                }
                else
                {
                    l.status = "Karar Ekle";
                    l.butontip = "btn btn-info";
                    l.durum = "Yok";
                }







                appler.Add(l);
            }

            sqlcon.Close();




            return appler;


        }




        //başvuruları arar
        public static List<basvur> appara(string isimsoyisim)
        {
            string arama = isimsoyisim + "%";
            List<basvur> appler = new List<basvur>();



            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from basvuru  as b left join karar as k on b.basvuruID=k.basvuruID where baglamadurumu=0 and  isim like @isimsoyisim", sqlcon);
            sqlcmd.Parameters.AddWithValue("@isimsoyisim", arama);





            sqlcon.Open();

            SqlDataReader sdr = sqlcmd.ExecuteReader();


            while (sdr.Read())
            {

                basvur l = new basvur();
                l.BasvuruID = Convert.ToInt32(sdr["basvuruID"]);

                l.Isim = sdr["isim"].ToString();
                l.esas = sdr["dosyano"].ToString();
                string karar = sdr["kararvarmi"].ToString();
                DateTime kd = Convert.ToDateTime(sdr["kayittarihi"]);
                l.Kayittarihi2 = kd.ToString("d MMM yyyy").Substring(0, 10);
               



                if (karar == "1")
                {
                    l.status = "Karar Güncelle";
                    l.butontip = "btn btn-warning";
                    l.durum = "Var";
                }
                else
                {
                    l.status = "Karar Ekle";
                    l.butontip = "btn btn-info";
                    l.durum = "Yok";
                }







                appler.Add(l);
            }

            sqlcon.Close();




            return appler;


        }
    }
}